import sqlite3
import re
from datetime import datetime


def parse_chat_file(file_path, db_path='data/diancan.db'):
    """
    解析聊天记录文件，提取(时间,姓名,内容)信息
    """
    records = []

    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    with open(file_path, 'r', encoding='utf-8') as file:
        lines = file.readlines()

    # 跳过文件头部分
    start_processing = False
    for line in lines:
        # 跳过文件头部信息
        if '=================================================================' in line:
            start_processing = True
            continue

        if not start_processing:
            continue

        # 使用正则表达式匹配时间|姓名:内容格式
        pattern = r'(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}) \| (.*?):(.*)'
        match = re.match(pattern, line.strip())

        if match:
            time_str = match.group(1)
            name = match.group(2).strip()
            content = match.group(3).strip()
            # 将时间字符串转换为时间戳
            timestamp = int(
                datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S').timestamp())

            # 根据昵称查询真实姓名
            cursor.execute("SELECT realname FROM realname WHERE nickname = ?",
                           (name, ))
            result = cursor.fetchone()
            if result:
                realname = result[0]  # 获取真实姓名
                records.append(
                    (timestamp, name, realname, content))  # 保存昵称和真实姓名
            else:
                records.append(
                    (timestamp, name, name, content))  # 没有找到真实姓名时，使用昵称

    # 关闭数据库连接
    conn.close()
    return records


def check_missing_data(file_path, db_path='data/diancan.db'):
    """
    检查diancan表中数据在同一天是否有缺失
    """
    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # 获取聊天记录中的所有数据
    chat_records = parse_chat_file(file_path, db_path)

    # 按日期分组聊天记录
    chat_by_date = {}
    for timestamp, nickname, realname, content in chat_records:
        date = datetime.fromtimestamp(timestamp).strftime('%Y-%m-%d')
        if date not in chat_by_date:
            chat_by_date[date] = []
        chat_by_date[date].append((timestamp, nickname, realname, content))

    # 检查每一天的数据
    for date, records in chat_by_date.items():
        print(f"检查日期: {date}")

        # 获取数据库中该日期的数据
        start_time = int(
            datetime.strptime(date + ' 00:00:00',
                              '%Y-%m-%d %H:%M:%S').timestamp())
        end_time = int(
            datetime.strptime(date + ' 23:59:59',
                              '%Y-%m-%d %H:%M:%S').timestamp())

        cursor.execute(
            "SELECT time, name, diancan FROM diancan WHERE time BETWEEN ? AND ?",
            (start_time, end_time))
        db_records = cursor.fetchall()

        # 将数据库记录转换为便于比较的格式
        db_record_set = set()
        for time, name, content in db_records:
            db_record_set.add((name, content))

        # 将聊天记录转换为便于比较的格式
        chat_record_set = set()
        chat_record_map = {}  # 保存完整信息用于打印
        for timestamp, nickname, realname, content in records:
            chat_record_set.add((realname, content))
            chat_record_map[(realname, content)] = (timestamp, nickname,
                                                    realname, content)

        # 找出缺失的记录
        missing_records = chat_record_set - db_record_set

        if missing_records:
            print(f"  缺失记录数量: {len(missing_records)}")
            for record in missing_records:
                timestamp, nickname, realname, content = chat_record_map[
                    record]
                actual_time = datetime.fromtimestamp(timestamp).strftime(
                    '%Y-%m-%d %H:%M:%S')
                print(
                    f"    缺失: 真实姓名={nickname}, 昵称={realname}, 时间={actual_time}, 内容={content}"
                )
        else:
            print("  无缺失记录")
        print()

    # 关闭数据库连接
    conn.close()


def insert_missing_data_for_date(file_path,
                                 target_date,
                                 db_path='data/diancan.db'):
    """
    插入指定日期的缺失记录到数据库中
    
    Args:
        file_path: 聊天记录文件路径
        target_date: 目标日期 (格式: 'YYYY-MM-DD')
        db_path: 数据库路径
    """
    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # 获取聊天记录中的所有数据
    chat_records = parse_chat_file(file_path, db_path)

    # 筛选出指定日期的记录
    target_records = []
    for timestamp, nickname, realname, content in chat_records:
        date = datetime.fromtimestamp(timestamp).strftime('%Y-%m-%d')
        if date == target_date:
            target_records.append((timestamp, nickname, realname, content))

    if not target_records:
        print(f"指定日期 {target_date} 没有找到聊天记录")
        conn.close()
        return

    print(f"处理日期: {target_date}")

    # 获取数据库中该日期的数据
    start_time = int(
        datetime.strptime(target_date + ' 00:00:00',
                          '%Y-%m-%d %H:%M:%S').timestamp())
    end_time = int(
        datetime.strptime(target_date + ' 23:59:59',
                          '%Y-%m-%d %H:%M:%S').timestamp())

    cursor.execute(
        "SELECT time, name, diancan FROM diancan WHERE time BETWEEN ? AND ?",
        (start_time, end_time))
    db_records = cursor.fetchall()

    # 将数据库记录转换为便于比较的格式
    db_record_set = set()
    for time, name, content in db_records:
        db_record_set.add((name, content))

    # 将聊天记录转换为便于比较的格式
    chat_record_set = set()
    chat_record_map = {}  # 保存完整信息用于插入
    for timestamp, nickname, realname, content in target_records:
        chat_record_set.add((realname, content))
        chat_record_map[(realname, content)] = (timestamp, nickname, realname,
                                                content)

    # 找出缺失的记录
    missing_records = chat_record_set - db_record_set

    if missing_records:
        print(f"  找到 {len(missing_records)} 条缺失记录，准备插入数据库...")
        inserted_count = 0
        for record in missing_records:
            timestamp, nickname, realname, content = chat_record_map[record]
            actual_time = datetime.fromtimestamp(timestamp).strftime(
                '%Y-%m-%d %H:%M:%S')

            try:
                cursor.execute(
                    "INSERT INTO diancan (time, name, diancan) VALUES (?, ?, ?)",
                    (timestamp, realname, content))
                print(
                    f"    已插入: 真实姓名={realname}, 昵称={nickname}, 时间={actual_time}, 内容={content}"
                )
                inserted_count += 1
            except sqlite3.Error as e:
                print(
                    f"    插入失败: 真实姓名={realname}, 昵称={nickname}, 时间={actual_time}, 内容={content}, 错误: {e}"
                )

        # 提交更改
        conn.commit()
        print(f"  成功插入 {inserted_count} 条记录")
    else:
        print("  无缺失记录需要插入")

    # 关闭数据库连接
    conn.close()


# 示例用法
if __name__ == "__main__":
    # 读取聊天记录文件
    chat_records = parse_chat_file(
        r"d:\myGitee\control-net\19460744006@chatroom-就餐群.txt")

    # 打印前几条记录
    for record in chat_records[:10]:
        timestamp, nickname, realname, content = record
        actual_time = datetime.fromtimestamp(timestamp).strftime(
            '%Y-%m-%d %H:%M:%S')
        print(
            f"时间: {actual_time}, 昵称: {nickname}, 真实姓名: {realname}, 内容: {content}"
        )

    # print("\n检查数据完整性:")
    # check_missing_data(r"d:\myGitee\control-net\19460744006@chatroom-就餐群.txt")

    # 插入指定日期的缺失记录示例（请根据需要修改日期）
    insert_missing_data_for_date(
        r"d:\myGitee\control-net\19460744006@chatroom-就餐群.txt", "2025-09-16")
